BeginTrans, CommitTrans, Rollback Methods Example

This example changes the job title of all sales representatives in the Employees table of the database. After the BeginTrans method starts a transaction that isolates all the changes made to the Employees table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved using the Update method. Furthermore, the main transaction is nested within another transaction that automatically rolls back any changes made by the user during this example.

One or more table pages remain locked while the user decides whether or not to accept the changes. For this reason, this technique isn't recommended but shown only as an example.

Sub BeginTransX()

   Dim strName As String
   Dim strMessage As String
   Dim wrkDefault As Workspace
   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset

   ' Get default Workspace.
   Set wrkDefault = DBEngine.Workspaces(0)
   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees")

   ' Start of outer transaction.
   wrkDefault.BeginTrans
   ' Start of main transaction.
   wrkDefault.BeginTrans

   With rstEmployees

      ' Loop through recordset and ask user if she wants to 
      ' change the title for a specified employee.
      Do Until .EOF
         If !Title = "Sales Representative" Then
            strName = !LastName & ", " & !FirstName
            strMessage = "Employee: " & strName & vbCr & _
               "Change title to Account Executive?"

            ' Change the title for the specified employee.
            If MsgBox(strMessage, vbYesNo) = vbYes Then
               .Edit
               !Title = "Account Executive"
               .Update
            End If
         End If

         .MoveNext
      Loop

      ' Ask if the user wants to commit to all the changes 
      ' made above.
      If MsgBox("Save all changes?", vbYesNo) = vbYes Then
         wrkDefault.CommitTrans
      Else
         wrkDefault.Rollback
      End If

      ' Print current data in recordset.
      .MoveFirst
      Do While Not .EOF
         Debug.Print !LastName & ", " & !FirstName & _
            " - " & !Title
         .MoveNext
      Loop

      ' Roll back any changes made by the user since this is 
      ' a demonstration.
      wrkDefault.Rollback
      .Close
   End With

   dbsNorthwind.Close

End Sub